find

我们使用的是MySQL数据库，为了提高运行效率，需要进行SQL方面的优化，我们主要采用以下几种方式进行SQL的优化：

一、查询所有时， 避免使用 * ，尽量用对应的字段代替。 二、选取最适用的字段属性，表中字段的宽度能小就尽量小*，避免不必要的内存消耗。 三、对查询进行优化，尽量避免全表扫描，因为全局查询的话就相当于将这张表进行一次全部的查询，影响效率。 四、添加索引。当然，索引也不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引。 五、当子查询和多表查询功能一样的时候，用多表查询。因为子查询实现查询，有两个from，操作了两次，而多表查询就一个from操作了一次。 六、对于多张大数据量的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。 七、尽量避免向客户端返回大量数据，若数据量过大，应该考虑相应需求是否合理。 八、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。 九、当我们查询的SQL语句过长的时候，我们先把固定的语句进行预编译存储到一个对象中，以后使用该对象高效地多次执行该语句。这样可以提高性能和安全性，并且可以增加代码的可读性和可维护性，减少数据库的压力。 如果面试官问，在项目中你们是怎样判断哪些sql语句是低效的？在项目中我们用explain指令分析sql语句的性能,explain能够显示出MySQL如何使用索引来处理select语句以及连接表。

mysql优化是一个综合性的技术，主要包括： 1、表的设计是否合理>>sql 2、索引的使用>>sql 3、分表技术 4、主从复制以及读写分离 5、存储过程 6、对mysql的配置优化 7、sql优化 8、mysql硬件升级 9、合理使用redis等非关系型数据库 10、合理使用solr等搜索应用服务器

1、表的设计是否合理 mysql表的优化设计分为下面几部分： 1、表设计是否符合三范式 2、字段的数据类型是否选取最优1.1、表结构是否符合三范式 首先说一下数据库设计的三范式： a.第一范式：确保每列保持原子性 b.第二范式：确保表中的每列都和主键相关 c.第三范式：确保每列都和主键列直接相关,而不是间接相关

1.1.1、确保每列保持原子性 第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。 第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

上表所示的用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。 上表如果不拆分，当我们需要查询“城市”部分时，只能使用“like”去做模糊查询，而like会进行全表扫描，会造成索引失效。 上表如果拆分了，当我们需要查询“城市”部分时，用“=”就可以满足，而“=”是精确查找，不会造成索引失效。

1.1.2、确保表中的每列都和主键相关 第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。 比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。 而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

这样设计，在很大程度上减小了数据库的冗余。减轻了单个表的访问压力，如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。

1.1.3、确保每列都和主键列直接相关,而不是间接相关 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。 比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。 如果将这2张表设计到一起，每次不管是对订单信息还是访问客户信息进行操作，都需要操作同一张表，数据少看不出来，一旦当数据量达到一定程度，比如上千万的数据。数据的操作会变的非常慢，单表压力太大。拆分之后可以大大减轻单表的压力。

1.1.4、适当拆分 当我们的表中存在类似于text或者很大的varchar类型的大字段的时候，如果我们访问这张表的时候大部分都不需要这个字段，将其拆分到另外的独立表中，减少占用的存储空间。提高内存的缓存命中率。

1.2、字段的数据类型是否选取最优 由于mysql是基于行（row）的数据库，数据库在操作IO的时候是以page的方式。而数据库操作中最为耗时的是IO处理，大部分数据库操作90%以上的时间都花在IO读写上面。所以尽可能减少IO读写量。1、数字类型： 非万不得已不要使用double,不仅仅是存储长度的问题，同时还存在精确性的问题。同样，固定精度的小数，也不建议使用decimal,建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本（例如在数据库中金额都是以分为单位存储）。 对应整数的存储，在数据量较大的情况下，建议区分开tinyint/int/bigint的选择，因为三者占用的存储空间也有很大的差别。2、字符类型： 非万不得已不要使用text数据类型，其处理方式决定了他的性能要低于char和varchar类型的处理。定长字段，建议使用char类型，不定长字段尽量使用varchar,且仅仅设定适当的最大长度。3、时间类型： 尽量使用timestamp类型，因为存储空间只需要datetime类型的一半。对于只需要精确到某一天的数据，建议使用date类型，因为它的存储空间只需要3个字节，比timestamp还少。（例如：存储生日字段，我们只需要存储到天，使用date类型最好，而对于平时的创建和更新时间字段，则使用timestamp类型，但是如果时间范围在’1970年’ 到 '2038年’之外的，则需要使用datetime类型。）4、blob: 强烈反对在数据库中存放BLOB 类型数据（能用来保存二进制，如图片）,用更适合的工具，才可以发挥到极致。例如我使用到了阿里的OSS。而且BLOB 类型数据的访问不是线程安全的，需要为其单独分配相应的数据库资源，并在操作完成后释放资源。

2、索引的使用 MySQL目前主要有以下几种索引类型： 1.普通索引 2.唯一索引 3.主键索引 4.组合索引 5.全文索引

2.1、普通索引 就经常使用到的字段，创建一个普通索引。这种索引也是我们经常用到的索引，比如按照姓名查找。在姓名字段上创建普通索引，注册时，判断手机号是否存在，在手机号字段上添加普通索引 创建普通索引 ALTER TABLE logs1 ADD INDEX index_htmlname(htmlname) 删除索引 DROP INDEX index_name ON table

2.2、唯一索引 与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。比如我们用户在注册时判断手机号是否已经注册，手机号这个字段就可以添加一个唯一索引。 创建唯一索引 ALTER TABLE logs1 ADD UNIQUE index_htmlname(htmlname) 分布式锁: 数据库实现分布式锁的时候，在method_name(方法名称)上面加了一个唯一索引。2.3、主键索引 是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

2.4、组合索引 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。比如在登陆日志（几千万数据)中我想知道某人在某年登陆了多少次。可以把username和time建立一个组合索引。 创建组合索引 ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。

3、分表技术 我们这里使用的数据库中间件mycat来实现数据的切分。根据其切分规则的类型，可以分为两种切分模式: 一种是按照不同的表来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分； 另外一种则是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数据的水平（横向）切分

3.1、垂直拆分 一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面。

优点： 拆分后业务清晰，拆分规则明确； 系统之间整合或扩展容易； 数据维护简单。 缺点： 部分业务表无法join，只能通过接口方式解决，提高了系统复杂度； 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高； 事务处理复杂（TCC）》》try confirm cancle

3.2、水平切分 由于垂直切分是按照业务的分类将表分散到不同的库，所以有些业务表会过于庞大，存在单库读写与存储瓶颈，所以就需要水平拆分来做解决。

典型拆分规则： 1、从会员的角度来分析，商户订单交易类系统中查询会员某天某月某个订单，那么就需要按照会员结合日期来拆分，不同的数据按照会员 ID 做分组，这样所有的数据查询 join 都会在单库内解决； 2、按照用户 ID 求模，将数据分散到不同的数据库，具有相同数据用户的数据都被分散到一个库中； 3、按照日期，将不同月甚至日的数据分散到不同的库中； 4、按照某个特定的字段求摸，或者根据特定范围段分散到不同的库中，具体需求，具体分析

优点： 拆分规则抽象好，join 操作基本可以数据库做； 不存在单库大数据，高并发的性能瓶颈； 应用端改造较少； 提高了系统的稳定性跟负载能力。 缺点： 拆分规则难以抽象； 分片事务一致性难以解决； 数据多次扩展难度跟维护量极大； 跨库 join 性能较差。

4、主从复制以及读写分离 如果对数据库的读和写都在同一个数据库服务器中操作，业务系统性能会降低。 为了提升业务系统性能，优化用户体验，可以通过做主从复制（读写分离）来减轻主数据库的负载。 而且表的数据量大，处理慢，CPU时不时100%，如果出现主数据库宕机，可快速将业务系统切换到从数据库上，可避免数据丢失。 因此，从保护数据库的角度来说，我们应该尽量避免没有主从复制机制的单节点数据库。

4.1、主从复制 对于 MySQL 来说，标准的读写分离是主从模式，一个写节点 Master 后面跟着多个读节点，读节点的数量取决于系统的压力。 MySQL Replication 可以将一个主数据库中的数据同步到一个或多个从数据库中（二进制日志）。并且这个同步过程默认以异步方式工作，不需要保持主从数据库的实时连接（即允许连接中断）。同时允许自定义配置需同步的数据库及数据表。 同步：发送马上收到结果。毛念民给张晓龙打电话借钱，张晓龙在电话里就回复了没钱。 异步：发送，过会收到结果。毛念民给张晓龙打电话借钱，张晓龙说我正忙呢，先挂了，一会回给你。一会给毛念民回电话：么钱！

5、存储过程 优点： 1、通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。 但是，MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。在编译存储过程之后，MySQL将其放入缓存中。MySQL为每个连接维护自己的存储过程高速缓存。如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。

2、存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。

3、存储的程序对任何应用程序都是可重用的和透明的。存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。

4、存储的程序是安全的。数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

缺点： 1、如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果您在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加，因为数据库服务器的设计不当于逻辑运算。 2、存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难。

3、很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。而且，MySQL不提供调试存储过程的功能。 4、开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。 这可能会导致应用程序开发和维护阶段的问题。

6、mysql配置优化 6.1：存储引擎选择 查看数据库的搜索引擎 show engines; MyISAM： 1、特性 不支持事务 表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小，但是也同时大大降低了其并发性能。 读写相互阻塞：不仅会在写的时候阻塞读取，MyISAM还会在读的时候阻塞写入，单读本身不会阻塞另外的读。 只会缓存索引：MyISAM可以用过key_buffer缓存大大提高访问性能减少磁盘IO,但这个缓存试试缓存索引，不会缓存数据。 2、使用场景 不需要事务支持 并发相对较低 数据修改相对较少 已读为主 数据一致性要求不高 InnoDB: 1、特性 具有较好的事务支持，支持4个事务隔离级别，支持多版本读 MySQL InnoDB事务的隔离级别有四级，默认是“可重复读”（REPEATABLE READ）。 未提交读：另一个事务修改了数据，但尚未提交，而本事务中的select会读到这些未被提交的数据（脏读：指一个线程中的事务读取到了另外一个线程中未提交的数据。）（隔离级别最低，并发性能高） 提交读：本事务读取到的是最新的数据（其他事务提交后的）。问题是，在同一个事务里，前后两次相同的select会读到不同的结果（不重复读：指一个线程中的事务读取到了另外一个线程中提交的update的数据。） 可重复读：在同一个事务里，select的结果是事务开始时时间点的状态，因此，同样的select操作读到的结果会是一致的。但是会有幻读现象（指一个线程中的事务读取到了另外一个线程中提交的insert的数据。） 串行化：读操作会隐式获取共享锁，可以保证不同事务间的互斥（锁表）。 行级锁定：通过索引实现，全表扫描仍然会是表锁。 读写阻塞与事务隔离级别有关 具有非常高效的缓存特性：能缓存索引，也能缓存数据 2、适用场景 需要事务支持 行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成的 数据更新较为频繁的场景 数据一致性要求高

6.2 、参数配置 InnoDB配置 innodb_file_per_table 表的数据和索引存放在共享表空间里或者单独表空间里。我们的工作场景安装是默认设置了innodb_file_per_table = ON，这样也有助于工作中进行单独表空间的迁移工作。MySQL 5.6中，这个属性默认值是ON。 _flush_log_at_trx_commit 默认值为1，表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的时候这个值是最合适的，比如在一个主节点上。但是对于磁盘（读写）速度较慢的系统，它会带来很巨大的开销，因为每次将改变flush到redo日志都需要额外的fsyncs。 如果将它的值设置为2会导致不太可靠（unreliable）。因为提交的事务仅仅每秒才flush一次到redo日志，但对于一些场景是可以接受的，比如对于主节点的备份节点这个值是可以接受的。如果值为0速度就更快了，但在系统崩溃时可能丢失一些数据：只适用于备份节点。 innodb_buffer_pool_size 这个参数应该是运维中必须关注的了。缓冲池是数据和索引缓存的地方，它属于MySQL的核心参数，默认为128MB，正常的情况下这个参数设置为物理内存的60%~70%。（不过我们的实例基本上都是多实例混部的，所以这个值还要根据业务规模来具体分析。） max_connections MySQL服务器默认连接数比较小，一般也就100来个最好把最大值设大一些。一般设置500~1000即可每一个链接都会占用一定的内存，所以这个参数也不是越大越好。有的人遇到too many connections会去增加这个参数的大小，但其实如果是业务量或者程序逻辑有问题或者sql写的不好，即使增大这个参数也无济于事，再次报错只是时间问题。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。https://www.jb51.net/article/144039.htm

7、SQL优化1、count(column) 和 count(*) 适用区别 count(column) 是表示结果集中有多少个column字段不为空的记录； count() 是表示整个结果集有多少条记录；2、order by 一定需要排序操作 利用索引来优化有排序需求的 SQL，是一个非常重要的优化手段3、不适用子查询 例：SELECT * FROM t1 WHERE id = (SELECT id FROM t2 WHERE name=’hechunyang’); 子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。 在MariaDB10/MySQL5.6版本里，采用join关联方式对其进行了优化，这条SQL会自动转换为 SELECT t1. FROM t1 JOIN t2 ON t1.id = t2.id; 但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，固生产环境应避免使用子查询 full join 、left join 、inner join 、 right join4、避免函数索引 例：SELECT * FROM t WHERE YEAR(createtime) >= 2016; 由于MySQL不像Oracle那样支持函数索引，即使d字段有索引，也会直接全表扫描。 应改为—–> SELECT * FROM t WHERE d >= ‘2016-01-01’;5、 用IN来替换OR 低效查询 SELECT * FROM t WHERE LOG_ID = 10 OR LOG_ID = 20 OR LOG_ID = 30; —–> 高效查询 SELECT * FROM t WHERE LOC_IN IN (10,20,30);6、LIKE双百分号无法使用到索引 SELECT * FROM t WHERE name LIKE ‘%de%’; —–> SELECT * FROM t WHERE name LIKE ‘de%’; 目前只有MySQL5.7支持全文索引（支持中文）7、分组统计可以禁止排序 SELECT goods_id,count() FROM t GROUP BY goods_id; 默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。 —–> SELECT goods_id,count() FROM t GROUP BY goods_id ORDER BY NULL;8、避免随机取记录 SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4; MySQL不支持函数索引，会导致全表扫描 —–> SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;9、禁止不必要的ORDER BY排序 SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC; —–> SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;10、批量INSERT插入 INSERT INTO t (id, name) VALUES(1,’Bea’); INSERT INTO t (id, name) VALUES(2,’Belle’); INSERT INTO t (id, name) VALUES(3,’Bernice’); —–> INSERT INTO t (id, name) VALUES(1,’Bea’), (2,’Belle’),(3,’Bernice’);11、explain+慢SQL分析 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是 如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 使用方式：Explain+SQL语句

8、mysql硬件升级

9、使用redis等非关系型数据库

9.1、redis解决的问题 1.少量数据存储，高速读写访问。比如抢购和秒杀 　 2.海量数据存储，分布式系统支持，数据一致性保证，方便的集群节点添加/删除。

9.2、redis特点： 1 、Redis不仅仅支持简单的k/v类型的数据，同时还提供list，set，zset，hash等数据结构的存储。 2 、Redis支持数据的备份，即master-slave模式的数据备份。 3 、Redis支持数据的持久化，可以将内存中的数据保持在磁盘中，重启的时候可以再次加载进行使用。aof和rdb

9.3、redis数据类型 Redis最为常用的数据类型主要有以下： String Hash List Set Sorted set

String： 是最常用的一种数据类型，普通的key/ value 存储都可以归为此类.即可以完全实现目前 Memcached 的功能，并且效率更高。还可以享受Redis的定时持久化，操作日志及 Replication等功能。

list： 的应用场景非常多，也是Redis最重要的数据结构之一，比如twitter的关注列表，粉丝列表等都可以用Redis的list结构来实现。

Set/Zset： 集合的概念就是一堆不重复值的组合。利用Redis提供的Sets数据结构，可以存储一些集合性的数据，比如在微博应用中，可以将一个用户所有的关注人存在一个集合中，将其所有粉丝存在一个集合。Redis还为集合提供了求交集、并集、差集等操作，可以非常方便的实现如共同关注、共同喜好、二度好友等功能，对上面的所有集合操作，你还可以使用不同的命令选择将结果返回给客户端还是存集到一个新的集合中。

10、合理使用solr等搜索应用服务器